<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>MySQL备份恢复数据 | lilei</title>
    <meta name="generator" content="VuePress 1.8.2">
    <link rel="icon" href="/images/icons/logo.png">
    <link rel="apple-touch-icon" href="/images/icons/logo.png">
    <link rel="manifest" href="/manifest.json">
    <script type="text/javascript" src="/assets/js/load.js"></script>
    <meta name="description" content="个人网站">
    <meta name="image" content="http://www.lileiblog.xyz/images/MySQL_binlog.png">
    <meta name="twitter:title" content="MySQL备份恢复数据">
    <meta name="twitter:description" content="">
    <meta name="twitter:card" content="summary_large_image">
    <meta name="twitter:image" content="http://www.lileiblog.xyz/images/MySQL_binlog.png">
    <meta name="twitter:url" content="http://www.lileiblog.xyz/SQL/MySQL%E5%A4%87%E4%BB%BD%E6%81%A2%E5%A4%8D%E6%95%B0%E6%8D%AE.html">
    <meta property="og:type" content="article">
    <meta property="og:title" content="MySQL备份恢复数据">
    <meta property="og:description" content="">
    <meta property="og:image" content="http://www.lileiblog.xyz/images/MySQL_binlog.png">
    <meta property="og:url" content="http://www.lileiblog.xyz/SQL/MySQL%E5%A4%87%E4%BB%BD%E6%81%A2%E5%A4%8D%E6%95%B0%E6%8D%AE.html">
    <meta property="og:site_name" content="lilei">
    <meta itemprop="name" content="MySQL备份恢复数据">
    <meta itemprop="description" content="">
    <meta itemprop="image" content="http://www.lileiblog.xyz/images/MySQL_binlog.png">
    <meta name="theme-color" content="#3eaf7c">
    <meta name="apple-mobile-web-app-capable" content="yes">
    <meta name="apple-mobile-web-app-status-bar-style" content="black">
    <meta name="msapplication-TileImage" content="/images/icons/logo.png">
    <meta name="msapplication-TileColor" content="#ffffff">
    <meta name="keywords" content="lilei,vue,vuepress,vuepress-theme,theme,主题,vuepress主题,blog
        ,vuepress-blog,java,python,shell,sql,golang,script,shell script,nginx,windows,去广告,androd,google drive
        ,one drive,idea,eclipse,git,小程序">
    <meta name="referrer" content="never">
    
    <link rel="preload" href="/assets/css/0.styles.8932179b.css" as="style"><link rel="preload" href="/assets/js/app.269009d6.js" as="script"><link rel="preload" href="/assets/js/3.974b9720.js" as="script"><link rel="preload" href="/assets/js/49.d812fdb2.js" as="script"><link rel="prefetch" href="/assets/js/10.5915e99b.js"><link rel="prefetch" href="/assets/js/11.ff732762.js"><link rel="prefetch" href="/assets/js/12.8bb86d91.js"><link rel="prefetch" href="/assets/js/13.18bde5b0.js"><link rel="prefetch" href="/assets/js/14.314fae34.js"><link rel="prefetch" href="/assets/js/15.0a4ff48f.js"><link rel="prefetch" href="/assets/js/16.ffaf1503.js"><link rel="prefetch" href="/assets/js/17.49a31a6e.js"><link rel="prefetch" href="/assets/js/18.7e6443bf.js"><link rel="prefetch" href="/assets/js/19.6880cc9a.js"><link rel="prefetch" href="/assets/js/20.1fb3f255.js"><link rel="prefetch" href="/assets/js/21.0cd39eea.js"><link rel="prefetch" href="/assets/js/22.392d85cb.js"><link rel="prefetch" href="/assets/js/23.f50adb15.js"><link rel="prefetch" href="/assets/js/24.43e324e1.js"><link rel="prefetch" href="/assets/js/25.e3663205.js"><link rel="prefetch" href="/assets/js/26.da141fb8.js"><link rel="prefetch" href="/assets/js/27.6c05235a.js"><link rel="prefetch" href="/assets/js/28.c124245a.js"><link rel="prefetch" href="/assets/js/29.d185870d.js"><link rel="prefetch" href="/assets/js/30.8587d422.js"><link rel="prefetch" href="/assets/js/31.dc382b56.js"><link rel="prefetch" href="/assets/js/32.20ba32e9.js"><link rel="prefetch" href="/assets/js/33.6ecd7de9.js"><link rel="prefetch" href="/assets/js/34.2b91c13a.js"><link rel="prefetch" href="/assets/js/35.66ed0733.js"><link rel="prefetch" href="/assets/js/36.ce7bca84.js"><link rel="prefetch" href="/assets/js/37.d00bb5ac.js"><link rel="prefetch" href="/assets/js/38.c3ecbb6f.js"><link rel="prefetch" href="/assets/js/39.308f4175.js"><link rel="prefetch" href="/assets/js/4.520f6ecc.js"><link rel="prefetch" href="/assets/js/40.f4b98bc2.js"><link rel="prefetch" href="/assets/js/41.95fae64f.js"><link rel="prefetch" href="/assets/js/42.fe4f877b.js"><link rel="prefetch" href="/assets/js/43.ca8f222e.js"><link rel="prefetch" href="/assets/js/44.4d4ee79e.js"><link rel="prefetch" href="/assets/js/45.79178b33.js"><link rel="prefetch" href="/assets/js/46.c3817d0b.js"><link rel="prefetch" href="/assets/js/47.b8252443.js"><link rel="prefetch" href="/assets/js/48.df9d5d3b.js"><link rel="prefetch" href="/assets/js/5.f1a24a16.js"><link rel="prefetch" href="/assets/js/50.55cfcaa8.js"><link rel="prefetch" href="/assets/js/51.b8608b02.js"><link rel="prefetch" href="/assets/js/52.52c5e00d.js"><link rel="prefetch" href="/assets/js/53.3a9bd5fb.js"><link rel="prefetch" href="/assets/js/54.a4240c13.js"><link rel="prefetch" href="/assets/js/55.01c4b876.js"><link rel="prefetch" href="/assets/js/56.f29549a3.js"><link rel="prefetch" href="/assets/js/57.01438a04.js"><link rel="prefetch" href="/assets/js/58.29bb4626.js"><link rel="prefetch" href="/assets/js/59.e8b89408.js"><link rel="prefetch" href="/assets/js/6.8c478b39.js"><link rel="prefetch" href="/assets/js/60.bedb5381.js"><link rel="prefetch" href="/assets/js/61.bdfbe584.js"><link rel="prefetch" href="/assets/js/62.cb825d1a.js"><link rel="prefetch" href="/assets/js/63.a9516572.js"><link rel="prefetch" href="/assets/js/64.aa854ed7.js"><link rel="prefetch" href="/assets/js/65.285f883d.js"><link rel="prefetch" href="/assets/js/66.12849f37.js"><link rel="prefetch" href="/assets/js/67.c18b00e4.js"><link rel="prefetch" href="/assets/js/68.fa59daa9.js"><link rel="prefetch" href="/assets/js/69.e9d5dda4.js"><link rel="prefetch" href="/assets/js/7.cfb0d0ea.js"><link rel="prefetch" href="/assets/js/70.db126cce.js"><link rel="prefetch" href="/assets/js/71.6acb04cd.js"><link rel="prefetch" href="/assets/js/72.7ff0ab11.js"><link rel="prefetch" href="/assets/js/73.9a66da79.js"><link rel="prefetch" href="/assets/js/74.0a38a030.js"><link rel="prefetch" href="/assets/js/75.7b32dbb3.js"><link rel="prefetch" href="/assets/js/8.cb9c08e5.js"><link rel="prefetch" href="/assets/js/9.ad652cfe.js"><link rel="prefetch" href="/assets/js/vendors~flowchart.110a1065.js">
    <link rel="stylesheet" href="/assets/css/0.styles.8932179b.css">
  </head>
  <body>
    <div id="app" data-server-rendered="true"><div class="theme-container"><header class="navbar"><div class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/" class="home-link router-link-active"><img src="/images/icons/logo.png" alt="lilei" class="logo"> <span class="site-name can-hide">lilei</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/Form/" class="nav-link">
  Form
</a></div><div class="nav-item"><a href="/IDE/" class="nav-link">
  IDE
</a></div><div class="nav-item"><a href="/Java/" class="nav-link">
  Java
</a></div><div class="nav-item"><a href="/Other/" class="nav-link">
  Other
</a></div><div class="nav-item"><a href="/Python/" class="nav-link">
  Python
</a></div><div class="nav-item"><a href="/SQL/" class="nav-link router-link-active">
  SQL
</a></div><div class="nav-item"><a href="/Shell/" class="nav-link">
  Shell
</a></div><div class="nav-item"><a href="/System/" class="nav-link">
  System
</a></div> <a href="https://github.com/woytu/notes-vuepress" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav></div></header> <div class="sidebar-mask"></div> <aside class="sidebar"><nav class="nav-links"><div class="nav-item"><a href="/Form/" class="nav-link">
  Form
</a></div><div class="nav-item"><a href="/IDE/" class="nav-link">
  IDE
</a></div><div class="nav-item"><a href="/Java/" class="nav-link">
  Java
</a></div><div class="nav-item"><a href="/Other/" class="nav-link">
  Other
</a></div><div class="nav-item"><a href="/Python/" class="nav-link">
  Python
</a></div><div class="nav-item"><a href="/SQL/" class="nav-link router-link-active">
  SQL
</a></div><div class="nav-item"><a href="/Shell/" class="nav-link">
  Shell
</a></div><div class="nav-item"><a href="/System/" class="nav-link">
  System
</a></div> <a href="https://github.com/woytu/notes-vuepress" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav>  <ul class="sidebar-links"><li><section class="sidebar-group depth-0"><p class="sidebar-heading open"><span>SQL</span> <!----></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/SQL/" aria-current="page" class="sidebar-link">SQL</a></li><li><a href="/SQL/MySQL事件.html" class="sidebar-link">MySQL事件</a></li><li><a href="/SQL/MySQL备份恢复数据.html" class="active sidebar-link">MySQL备份恢复数据</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/SQL/MySQL备份恢复数据.html#binlog2sql" class="sidebar-link">binlog2sql</a></li><li class="sidebar-sub-header"><a href="/SQL/MySQL备份恢复数据.html#mysqldump" class="sidebar-link">mysqldump</a></li><li class="sidebar-sub-header"><a href="/SQL/MySQL备份恢复数据.html#source命令导入" class="sidebar-link">source命令导入</a></li><li class="sidebar-sub-header"><a href="/SQL/MySQL备份恢复数据.html#主从同步" class="sidebar-link">主从同步</a></li></ul></li><li><a href="/SQL/MySQL存储过程.html" class="sidebar-link">MySQL存储过程</a></li><li><a href="/SQL/MySQL安装配置.html" class="sidebar-link">MySQL安装配置</a></li><li><a href="/SQL/MySQL常见问题.html" class="sidebar-link">MySQL常见问题</a></li><li><a href="/SQL/MySQL笔记.html" class="sidebar-link">MySQL笔记</a></li><li><a href="/SQL/mysql-udf安装.html" class="sidebar-link">mysql-udf安装</a></li></ul></section></li></ul> </aside> <main class="page"> <div class="theme-default-content content__default"><h1 id="mysql备份恢复数据"><a href="#mysql备份恢复数据" class="header-anchor">#</a> MySQL备份恢复数据</h1> <p></p><div class="table-of-contents"><ul><li><a href="#binlog2sql">binlog2sql</a><ul><li><a href="#mysql必须设置参数">MySQL必须设置参数</a></li><li><a href="#user需要的最小权限集合">user需要的最小权限集合</a></li><li><a href="#查看目前的binlog文件">查看目前的binlog文件</a></li><li><a href="#定位误操作sql的binlog位置">定位误操作SQL的binlog位置</a></li><li><a href="#生成sql文件回滚">生成sql文件回滚</a></li><li><a href="#不生成sql文件回滚">不生成sql文件回滚</a></li></ul></li><li><a href="#mysqldump">mysqldump</a><ul><li><a href="#mysqldump导出">mysqldump导出</a></li><li><a href="#mysqldump导入">mysqldump导入</a></li><li><a href="#mysqldump两台主机同步备份">mysqldump两台主机同步备份</a></li></ul></li><li><a href="#source命令导入">source命令导入</a></li><li><a href="#主从同步">主从同步</a><ul><li><a href="#配置主服务器">配置主服务器</a></li><li><a href="#配置从服务器">配置从服务器</a></li><li><a href="#如果出现以下错误">如果出现以下错误</a></li></ul></li></ul></div><p></p> <h2 id="binlog2sql"><a href="#binlog2sql" class="header-anchor">#</a> binlog2sql</h2> <blockquote><p>使用此方式之前一定是MySQL开启了bin-log的才可行，如果没有安装开源工具<code>binlog2sql</code>那么请安装。</p></blockquote> <blockquote><p>binlog2sql是一款简单易用的binlog解析工具，其中一个功能就是生成回滚SQL。</p></blockquote> <ul><li><a href="https://github.com/danfengcao/binlog2sql" target="_blank" rel="noopener noreferrer">https://github.com/danfengcao/binlog2sql<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul> <div class="language-bash extra-class"><pre class="language-bash"><code><span class="token function">git</span> clone https://github.com/danfengcao/binlog2sql.git
<span class="token builtin class-name">cd</span> binlog2sql/
pip <span class="token function">install</span> -r requirements.txt
</code></pre></div><h3 id="mysql必须设置参数"><a href="#mysql必须设置参数" class="header-anchor">#</a> MySQL必须设置参数</h3> <div class="language-bash extra-class"><pre class="language-bash"><code><span class="token punctuation">[</span>mysqld<span class="token punctuation">]</span>
server_id <span class="token operator">=</span> <span class="token number">1</span>
log_bin <span class="token operator">=</span> /var/log/mysql/mysql-bin.log
max_binlog_size <span class="token operator">=</span> 1G
binlog_format <span class="token operator">=</span> row
binlog_row_image <span class="token operator">=</span> full
</code></pre></div><h3 id="user需要的最小权限集合"><a href="#user需要的最小权限集合" class="header-anchor">#</a> user需要的最小权限集合</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">select</span><span class="token punctuation">,</span> super<span class="token operator">/</span><span class="token keyword">replication</span> client<span class="token punctuation">,</span> <span class="token keyword">replication</span> slave
<span class="token comment">#建议授权</span>
<span class="token keyword">GRANT</span> <span class="token keyword">SELECT</span><span class="token punctuation">,</span> <span class="token keyword">REPLICATION</span> SLAVE<span class="token punctuation">,</span> <span class="token keyword">REPLICATION</span> CLIENT <span class="token keyword">ON</span> <span class="token operator">*</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> 
</code></pre></div><h3 id="查看目前的binlog文件"><a href="#查看目前的binlog文件" class="header-anchor">#</a> 查看目前的binlog文件</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">show</span> master logs<span class="token punctuation">;</span>
</code></pre></div><ul><li>查询结果</li></ul> <table><thead><tr><th>Log_name</th> <th>File_size</th></tr></thead> <tbody><tr><td>mysql-bin.000001</td> <td>177</td></tr> <tr><td>mysql-bin.000002</td> <td>437</td></tr> <tr><td>mysql-bin.000003</td> <td>685</td></tr> <tr><td>mysql-bin.000004</td> <td>1702399</td></tr> <tr><td>mysql-bin.000005</td> <td>963964</td></tr> <tr><td>mysql-bin.000006</td> <td>13118637</td></tr> <tr><td>mysql-bin.000007</td> <td>26765043</td></tr></tbody></table> <h3 id="定位误操作sql的binlog位置"><a href="#定位误操作sql的binlog位置" class="header-anchor">#</a> 定位误操作SQL的binlog位置</h3> <blockquote><p>可以看到最新的binlog文件是mysql-bin.000007，我们再定位误操作SQL的binlog位置</p></blockquote> <div class="language-bash extra-class"><pre class="language-bash"><code><span class="token comment"># `binlog2sql.py`文件在`binlog2sql/binlog2sql/`文件夹下</span>
<span class="token comment"># 如果命令最后不加时间可输出最近操作的SQL命令</span>
python binlog2sql/binlog2sql.py -h127.0.0.1 -P端口 -u账号 -p<span class="token string">'密码'</span> <span class="token punctuation">\</span>
    -d数据库 -t表 --start-file<span class="token operator">=</span><span class="token string">'binlog文件'</span> <span class="token punctuation">\</span>
    --start-datetime<span class="token operator">=</span><span class="token string">'之前误操作的SQL执行开始时间'</span> <span class="token punctuation">\</span>
    --stop-datetime<span class="token operator">=</span><span class="token string">'之前误操作的SQL执行结束时间'</span>
</code></pre></div><blockquote><p>当程序跑完之后看最后一条数据最末尾：<code>#start 5117865 end 13679060 time 2018-06-12 10:07:53</code>
开始位置（start 5117865）和结束位置（end 13679060）</p></blockquote> <p><img src="/images/MySQL_binlog.png" alt=""></p> <h3 id="生成sql文件回滚"><a href="#生成sql文件回滚" class="header-anchor">#</a> 生成sql文件回滚</h3> <blockquote><p>生成<code>rollback.sql</code>文件，并检查回滚SQL是否正确</p></blockquote> <div class="language-bash extra-class"><pre class="language-bash"><code>python binlog2sql/binlog2sql.py -h127.0.0.1 -P端口 -u账号 -p<span class="token string">'密码'</span> <span class="token punctuation">\</span>
    -d数据库 -t表 --start-file<span class="token operator">=</span><span class="token string">'binlog文件'</span> --start-position<span class="token operator">=</span>开始位置 <span class="token punctuation">\</span>
    --stop-position<span class="token operator">=</span>结束位置 -B <span class="token operator">&gt;</span> rollback.sql
</code></pre></div><blockquote><p>查看当前文件夹下是否生成了rollback.sql文件,确认回滚SQL正确，如果有就执行回滚</p></blockquote> <div class="language-bash extra-class"><pre class="language-bash"><code>mysql -h127.0.0.1 -P端口 -u账号 -p<span class="token string">'密码'</span> <span class="token operator">&lt;</span> rollback.sql
</code></pre></div><h3 id="不生成sql文件回滚"><a href="#不生成sql文件回滚" class="header-anchor">#</a> 不生成sql文件回滚</h3> <blockquote><p>不生成rollback.sql文件，执行命令后在输出中检查回滚SQL是否正确</p></blockquote> <div class="language-bash extra-class"><pre class="language-bash"><code>python binlog2sql/binlog2sql.py -h127.0.0.1 -P端口 -u账号 -p<span class="token string">'密码'</span> <span class="token punctuation">\</span>
    -d数据库 -t表 --start-file<span class="token operator">=</span><span class="token string">'binlog文件'</span> --start-position<span class="token operator">=</span>开始位置 <span class="token punctuation">\</span>
    --stop-position<span class="token operator">=</span>结束位置 -B
</code></pre></div><blockquote><p>确认回滚SQL正确，执行回滚语句。登录MySQL确认，数据回滚成功。</p></blockquote> <div class="language-bash extra-class"><pre class="language-bash"><code>python binlog2sql/binlog2sql.py -h127.0.0.1 -P端口 -u账号 -p<span class="token string">'密码'</span> <span class="token punctuation">\</span>
    -d数据库 -t表 --start-file<span class="token operator">=</span><span class="token string">'binlog文件'</span> --start-position<span class="token operator">=</span>开始位置 <span class="token punctuation">\</span>
    --stop-position<span class="token operator">=</span>结束位置 -B <span class="token operator">|</span> mysql -h127.0.0.1 -P端口 -u账号 -p<span class="token string">'密码'</span>
</code></pre></div><h2 id="mysqldump"><a href="#mysqldump" class="header-anchor">#</a> mysqldump</h2> <blockquote><p>如果是在本机上备份本机的数据库地址和端口可以不要，如果是在本机上备份其他主机上的数据库就需要地址和端口</p></blockquote> <ul><li><a href="/files/MySQL%E5%A4%87%E4%BB%BD%E5%B9%B6%E5%88%A0%E9%99%A4%E5%8E%86%E5%8F%B2.sh">MySQL备份并删除历史.sh</a></li> <li><a href="/files/MySQL%E5%90%8C%E6%AD%A5%E8%BF%9C%E7%A8%8B%E6%95%B0%E6%8D%AE%E5%BA%93%E5%88%B0%E6%9C%AC%E5%9C%B0.sh">MySQL同步远程数据库到本地.sh</a></li></ul> <blockquote><p><code>mysqldump</code>命令需要手动输入密码，所以一般不输入<code>-p</code>参数</p></blockquote> <ul><li><a href="https://blog.csdn.net/hao7030187/article/details/77839968" target="_blank" rel="noopener noreferrer">mysqldump数据导出参数说明<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li> <li><a href="http://www.cnblogs.com/chenmh/p/5300370.html" target="_blank" rel="noopener noreferrer">mysqldump数据导出参数说明<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul> <p><strong>参数说明</strong></p> <ul><li><code>-d</code> 结构(--no-data:不导出任何数据，只导出数据库表结构)</li> <li><code>-t</code> 数据(--no-create-info:只导出数据，而不添加CREATE TABLE 语句)</li> <li><code>-n</code> (--no-create-db:只导出数据，而不添加CREATE DATABASE 语句）</li> <li><code>-R</code> (--routines:导出存储过程以及自定义函数)</li> <li><code>-E</code> (--events:导出事件)</li> <li><code>--triggers</code> (默认导出触发器，使用--skip-triggers屏蔽导出)</li> <li><code>-B</code> (--databases:导出数据库列表，单个库时可省略）</li> <li><code>--tables</code> 表列表（单个表时可省略）
<ul><li>①同时导出结构以及数据时可同时省略-d和-t</li> <li>②同时不导出结构和数据可使用-ntd</li> <li>③只导出存储过程和函数可使用-R -ntd</li> <li>④导出所有(结构&amp;数据&amp;存储过程&amp;函数&amp;事件&amp;触发器)使用-R -E(相当于①，省略了-d -t;触发器默认导出)</li> <li>⑤只导出结构&amp;函数&amp;事件&amp;触发器使用 -R -E -d</li></ul></li> <li><code>--opt</code>等同于以下参数，该选项默认开启， 可以用--skip-opt禁用
<ul><li><code>--add-drop-table</code></li> <li><code>--add-locks</code></li> <li><code>--create-options</code></li> <li><code>--quick</code></li> <li><code>--extended-insert</code></li> <li><code>--lock-tables</code></li> <li><code>--set-charset</code></li> <li><code>--disable-keys</code></li></ul></li></ul> <ol><li>--opt 在创建表结构之前 会有 DROP TABLE IF EXISTS</li> <li>原表在创建的时候指定了AUTO_INCREMENT，在使用了--opt 仍然和建表时候一样存在参数，在使用参数--skip-opt的时候，忽略了此参数AUTO_INCREMENT</li> <li>在使用参数--opt的时候，创建表的类型，字符集等等都是默认参数ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;当使用了--skip-opt的时候，这些参数都给忽略了</li> <li>导出原表中的数据，--opt是一个insert多个value，在使用了--skip-opt的时候，是多个insert组成的；</li></ol> <h3 id="mysqldump导出"><a href="#mysqldump导出" class="header-anchor">#</a> mysqldump导出</h3> <div class="language-bash extra-class"><pre class="language-bash"><code><span class="token comment"># 只导出结构&amp;函数&amp;事件&amp;触发器使用</span>
mysqldump -R -E -d -h需要备份的主机地址 -P端口 -u用户名 -p 数据库名 <span class="token operator">&gt;</span> /home/backup.sql

<span class="token comment"># 只导出存储过程和函数可使用</span>
mysqldump -R -ntd -h需要备份的主机地址 -P端口 -u用户名 -p 数据库名 <span class="token operator">&gt;</span> /home/backup.sql

<span class="token comment"># 导出单个数据库中所有(结构&amp;数据&amp;存储过程&amp;函数&amp;事件&amp;触发器)到sql文件</span>
mysqldump -R -E -h需要备份的主机地址 -P端口 -u用户名 -p 数据库名 <span class="token operator">&gt;</span> /home/backup.sql

<span class="token comment"># mysqldump 备份并压缩sql文件</span>
mysqldump -R -E -h主机地址 -P端口 -u用户名 -p 数据库名 <span class="token operator">|</span> <span class="token function">gzip</span> <span class="token operator">&gt;</span> /home/backup.sql.gz

<span class="token comment"># 备份所有的数据库到一个sql文件</span>
mysqldump -R -E -h主机地址 -P端口 -u用户名 -p --all-databases <span class="token operator">&gt;</span> /home/all.sql

<span class="token comment"># 从一个数据库导出到另一个数据库</span>
mysqldump -R -E -u用户名 -p 数据库名 <span class="token operator">|</span> mysql 新数据库名 -u用户名 -p密码
</code></pre></div><ul><li>如果提示如下错误，执行<code>vi /etc/my.cnf</code></li></ul> <blockquote><p><code>mysqldump: [Warning] Using a password on the command line interface can be insecure</code></p></blockquote> <div class="language-ini extra-class"><pre class="language-ini"><code><span class="token comment"># [mysqldump]</span>
<span class="token header"><span class="token punctuation">[</span><span class="token section-name selector">client</span><span class="token punctuation">]</span></span>
<span class="token comment"># 查看mysql.scok位置 netstat -ln | grep mysql</span>
<span class="token comment">#socket = /tmp/mysql.scok</span>
<span class="token key attr-name">default-character-set</span> <span class="token punctuation">=</span> <span class="token value attr-value">utf8mb4</span>
<span class="token key attr-name">host</span> <span class="token punctuation">=</span> <span class="token value attr-value">127.0.0.1</span>
<span class="token key attr-name">user</span> <span class="token punctuation">=</span> <span class="token value attr-value">root</span>
<span class="token key attr-name">password</span> <span class="token punctuation">=</span> <span class="token value attr-value">'<span class="token inner-value">123</span>'</span>
</code></pre></div><div class="language-bash extra-class"><pre class="language-bash"><code><span class="token comment"># 导出</span>
mysqldump --defaults-extra-file<span class="token operator">=</span>/etc/my.cnf 新数据库名 <span class="token operator">&gt;</span> database.sql
<span class="token comment"># 导入</span>
mysql --defaults-extra-file<span class="token operator">=</span>/etc/my.cnf 新数据库名 <span class="token operator">&lt;</span> database.sql
</code></pre></div><div class="language-batch extra-class"><pre class="language-batch"><code><span class="token operator">@</span><span class="token command"><span class="token keyword">echo</span> off</span>

<span class="token comment">:: 存储sql脚本文件的目录</span>
<span class="token command"><span class="token keyword">set</span> <span class="token string">&quot;dirPath=D:\mysql_backup&quot;</span></span>
<span class="token comment">:: 数据库名</span>
<span class="token command"><span class="token keyword">set</span> <span class="token string">&quot;databaseName=demo&quot;</span></span>
<span class="token comment">:: 设置时间变量</span>
<span class="token command"><span class="token keyword">set</span> <span class="token string">&quot;Ymd=%date:~0,4%%date:~5,2%%date:~8,2%&quot;</span></span>

<span class="token comment">:: 创建存储的文件夹</span>
<span class="token command"><span class="token keyword">if</span> <span class="token keyword">not</span> <span class="token keyword">exist</span> <span class="token variable">%dirPath%</span></span> <span class="token command"><span class="token keyword">md</span> <span class="token variable">%dirPath%</span></span>

<span class="token comment">:: 执行备份操作</span>
<span class="token command"><span class="token keyword">mysqldump</span> <span class="token parameter attr-name">--opt</span> <span class="token parameter attr-name">--user</span>=root <span class="token parameter attr-name">--password</span>=root <span class="token parameter attr-name">--host</span>=<span class="token number">127</span>.<span class="token number">0</span>.<span class="token number">0</span>.<span class="token number">1</span> <span class="token parameter attr-name">--protocol</span>=tcp <span class="token operator">^</span>
    <span class="token parameter attr-name">--port</span>=<span class="token number">3306</span> <span class="token parameter attr-name">--default-character-set</span>=utf8 <span class="token parameter attr-name">--single-transaction</span>=TRUE <span class="token operator">^</span>
    <span class="token parameter attr-name">--routines</span> <span class="token parameter attr-name">--events</span> <span class="token variable">%databaseName%</span> &gt;D:\mysql_backup\backup_demo_<span class="token variable">%Ymd%</span>.sql</span>

<span class="token comment">:: 从文件夹或树中选择要进行批处理的文件，删除两周前的备份数据</span>
<span class="token command"><span class="token keyword">forfiles</span> <span class="token parameter attr-name">/p</span> <span class="token variable">%dirPath%</span> <span class="token parameter attr-name">/m</span> backup_*.sql <span class="token parameter attr-name">-d</span> <span class="token number">-14</span> <span class="token parameter attr-name">/c</span> <span class="token string">&quot;cmd /c del /f @path&quot;</span></span>
</code></pre></div><h3 id="mysqldump导入"><a href="#mysqldump导入" class="header-anchor">#</a> mysqldump导入</h3> <div class="language-bash extra-class"><pre class="language-bash"><code><span class="token comment"># 用mysqldump导入本地sql文件</span>
mysqldump -h主机地址 -P端口 -u用户名 数据库名 <span class="token operator">&lt;</span> /home/backup.sql

<span class="token comment"># mysql直接用压缩文件恢复</span>
gunzip <span class="token operator">&lt;</span> backup.sql.gz <span class="token operator">|</span> mysql -h主机地址 -P端口 -u用户名 -p密码 数据库名

<span class="token comment"># mysql从本地sql文件导入</span>
mysql -h主机地址 -P端口 -u用户名 -p密码 数据库名 <span class="token operator">&lt;</span> backupfile.sql
</code></pre></div><h3 id="mysqldump两台主机同步备份"><a href="#mysqldump两台主机同步备份" class="header-anchor">#</a> mysqldump两台主机同步备份</h3> <div class="language-bash extra-class"><pre class="language-bash"><code>mysqldump -R -E -h导出的主机地址 -P端口 -u用户名 -p 数据库名 <span class="token operator">|</span> mysql -h导入的主机地址 -P端口 -u用户名 -p密码 -C 数据库名
</code></pre></div><h2 id="source命令导入"><a href="#source命令导入" class="header-anchor">#</a> source命令导入</h2> <div class="language-bash extra-class"><pre class="language-bash"><code><span class="token comment"># 进入MySQL并指定数据库</span>
use 数据库名<span class="token punctuation">;</span>
<span class="token comment"># 导入数据（注意sql文件的路径）</span>
<span class="token builtin class-name">source</span> /home/backup.sql<span class="token punctuation">;</span>
</code></pre></div><h2 id="主从同步"><a href="#主从同步" class="header-anchor">#</a> 主从同步</h2> <h3 id="配置主服务器"><a href="#配置主服务器" class="header-anchor">#</a> 配置主服务器</h3> <p><strong>在[mysqld]节点下按需添加</strong></p> <div class="language-conf extra-class"><pre class="language-text"><code>[mysqld]
# 设置server_id，一般设置为IP,注意要唯一
server_id=100
# 复制过滤：也就是指定数据库不用同步,多个之间用','号分割
binlog-ignore-db=mysql,information_schema,preformance_schema
# 指定需要同步的二进制数据库
binlog-do-db=test
# 开启二进制日志功能，可以随便取，最好有含义（关键就是这里了）
log-bin=edu-mysql-bin
# 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
# 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed
# 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=5
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
</code></pre></div><p><strong>创建数据同步用户</strong></p> <blockquote><p>这里主要是要授予用户REPLICATION SLAVE权限和REPLICATION CLIENT权限</p></blockquote> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">USER</span> <span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span> <span class="token string">'slave'</span><span class="token variable">@'%'</span> IDENTIFIED <span class="token keyword">BY</span> <span class="token string">'密码'</span><span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token keyword">REPLICATION</span> SLAVE<span class="token punctuation">,</span> <span class="token keyword">REPLICATION</span> CLIENT <span class="token keyword">ON</span> <span class="token operator">*</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> <span class="token string">'slave'</span><span class="token variable">@'%'</span><span class="token punctuation">;</span>
FLUSH <span class="token keyword">PRIVILEGES</span><span class="token punctuation">;</span>
</code></pre></div><h3 id="配置从服务器"><a href="#配置从服务器" class="header-anchor">#</a> 配置从服务器</h3> <p><strong>在[mysqld]节点下按需添加</strong></p> <blockquote><p>从库中的<code>server-id</code>值一定不要跟主库的一样</p></blockquote> <div class="language-conf extra-class"><pre class="language-text"><code>[mysqld]
# 设置server_id，一般设置为IP,注意要唯一
server_id=101
# 复制过滤：也就是指定数据库不用同步,多个之间用','号分割
binlog-ignore-db=mysql,information_schema,preformance_schema
# 指定需要同步的二进制数据库
binlog-do-db=test
# 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
log-bin=edu-mysql-slave1-bin
# 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
# 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed
# 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=5
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
# relay_log配置中继日志
relay_log=edu-mysql-relay-bin
# log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
# 防止改变数据(除了特殊的线程)
read_only=1
</code></pre></div><p><strong>先查询主服务器当前二进制log文件</strong></p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">SHOW</span> MASTER <span class="token keyword">STATUS</span><span class="token punctuation">;</span>
</code></pre></div><ul><li>查询出来如下</li></ul> <table><thead><tr><th>File</th> <th>Position</th> <th>Binlog_Do_DB</th> <th>Binlog_Ignore_DB</th> <th>Executed_Gtid_Set</th></tr></thead> <tbody><tr><td>mysql-bin.000025</td> <td>154</td> <td>ichangg_im</td> <td></td> <td></td></tr></tbody></table> <p><strong>进入从服务器MySQL执行以下命令</strong></p> <div class="language-sql extra-class"><pre class="language-sql"><code>CHANGE MASTER <span class="token keyword">TO</span> MASTER_HOST<span class="token operator">=</span><span class="token string">'主服务器IP'</span><span class="token punctuation">,</span>MASTER_PORT<span class="token operator">=</span><span class="token number">3306</span><span class="token punctuation">,</span>MASTER_USER<span class="token operator">=</span><span class="token string">'主服务器同步用户名'</span><span class="token punctuation">,</span>MASTER_PASSWORD<span class="token operator">=</span><span class="token string">'密码'</span><span class="token punctuation">,</span>MASTER_LOG_FILE<span class="token operator">=</span><span class="token string">'主MySQL二进制文件名'</span><span class="token punctuation">,</span>MASTER_LOG_POS<span class="token operator">=</span>Position字段中数据<span class="token punctuation">,</span>MASTER_CONNECT_RETRY<span class="token operator">=</span><span class="token number">30</span><span class="token punctuation">;</span>
</code></pre></div><ul><li><code>MASTER_HOST='192.168.1.100'</code> #Master的IP地址</li> <li><code>MASTER_USER='slave'</code> #用于同步数据的用户（在Master中授权的用户）</li> <li><code>MASTER_PASSWORD='123456'</code> #同步数据用户的密码</li> <li><code>MASTER_PORT=3306</code> #Master数据库服务的端口</li> <li><code>MASTER_LOG_FILE='edu-mysql-bin.000001'</code> #指定Slave从哪个日志文件开始读复制数据（Master上执行命令的结果的File字段）</li> <li><code>MASTER_LOG_POS=429</code> #从哪个POSITION号开始读（Master上执行命令的结果的Position字段）</li> <li><code>MASTER_CONNECT_RETRY=30</code> #当建立主从连接时，如果连接建立失败，间隔多久后重试。单位为秒，默认设置为60秒，同步延迟调优参数。</li></ul> <p><strong>查看主从同步状态</strong></p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">show</span> slave <span class="token keyword">status</span><span class="token punctuation">;</span>
</code></pre></div><p><strong>开启复制</strong></p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">START</span> SLAVE<span class="token punctuation">;</span>
</code></pre></div><h3 id="如果出现以下错误"><a href="#如果出现以下错误" class="header-anchor">#</a> 如果出现以下错误</h3> <blockquote><p><code>ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository</code></p></blockquote> <blockquote><p>看样子是找不到中继日志的仓库，但是查看变量<code>relay log</code>的位置是设置了的</p></blockquote> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">show</span> variables <span class="token operator">like</span> <span class="token string">'relay%'</span><span class="token punctuation">;</span>
</code></pre></div><ul><li>重置复制信息</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code>RESET MASTER<span class="token punctuation">;</span>
</code></pre></div><ul><li>查看主从复制是否还有主从配置</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">SHOW</span> SLAVE <span class="token keyword">STATUS</span>\G
</code></pre></div><ul><li>如果还有就执行以下命令清除所有</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code>RESET SLAVE <span class="token keyword">ALL</span><span class="token punctuation">;</span>
</code></pre></div><ul><li>再次开启复制</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">START</span> SLAVE<span class="token punctuation">;</span>
</code></pre></div><ul><li>查看主从复制是否成功</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">SHOW</span> SLAVE <span class="token keyword">STATUS</span>\G
</code></pre></div><p><strong>Operation CREATE USER failed</strong></p> <blockquote><p><code>Error 'Operation CREATE USER failed for 'slave'@'%'' on query. Default database: ''. Query:</code></p> <blockquote><p><code>'CREATE USER 'slave'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*040A65A51A0B047A826CDE05448536015D471E15''</code></p></blockquote></blockquote> <ul><li>先执行以下命令</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code>STOP SLAVE<span class="token punctuation">;</span>
FLUSH <span class="token keyword">PRIVILEGES</span><span class="token punctuation">;</span>
<span class="token keyword">START</span> SLAVE<span class="token punctuation">;</span>
</code></pre></div><ul><li>如果错误仍然存在，执行以下命令</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code>STOP SLAVE<span class="token punctuation">;</span>
<span class="token keyword">DROP</span> <span class="token keyword">USER</span> <span class="token string">'slave'</span><span class="token variable">@'%'</span><span class="token punctuation">;</span>
<span class="token keyword">START</span> SLAVE<span class="token punctuation">;</span>
</code></pre></div></div> <footer class="page-edit"><div class="edit-link"><a href="https://github.com/woytu/notes-vuepress/edit/master//SQL/MySQL备份恢复数据.md" target="_blank" rel="noopener noreferrer">在 GitHub 上编辑此页</a> <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></div> <div class="last-updated"><span class="prefix">上次更新:</span> <span class="time">2021-05-31 16:55:29</span></div></footer> <div class="page-nav"><p class="inner"><span class="prev">
      ←
      <a href="/SQL/MySQL事件.html" class="prev">
        MySQL事件
      </a></span> <span class="next"><a href="/SQL/MySQL存储过程.html">
        MySQL存储过程
      </a>
      →
    </span></p></div> </main></div><div class="global-ui"><!----></div></div>
    <script src="/assets/js/app.269009d6.js" defer></script><script src="/assets/js/3.974b9720.js" defer></script><script src="/assets/js/49.d812fdb2.js" defer></script>
  </body>
</html>
